-- 创建数据库并指定中文字符集
CREATE DATABASE SPIDER_DB
ON
(
    NAME = SPIDER_DB,
    FILENAME = '/var/opt/mssql/SPIDER_DB.mdf',
    SIZE = 5,
    MAXSIZE = 20400
)
LOG ON
(
    NAME = SPIDER_DB_LOG,
    FILENAME = '/var/opt/mssql/SPIDER_DB_LOG.ldf',
    SIZE = 64,
    MAXSIZE = 512
)
COLLATE Chinese_PRC_CI_AS

-- 为数据库启用查询存储
IF SERVERPROPERTY('ProductVersion') > '12'
BEGIN
    ALTER DATABASE [SPIDER_DB] SET QUERY_STORE=ON;
END
GO;

-- 查询当前数据库都有哪些表
SELECT NAME FROM sysobjects WHERE XTYPE = 'U';

-- 查看指定的表有哪些字段
DECLARE @tb_name VARCHAR(128) = 'TEST_TABLE'
SELECT NAME FROM sys.columns WHERE OBJECT_ID = (
    SELECT ID FROM sysobjects WHERE XTYPE = 'U' AND NAME = @tb_name
);

-- 查询指定的表被哪些数据库对象引用
DECLARE @tb_name VARCHAR(128) = 'TEST_TABLE'
SELECT OBJECT_NAME(ID) FROM sys.syscomments WHERE TEXT LIKE '%' + @tb_name + '%';

-- 查询数据库是否存在
SELECT 1 FROM sys.databases WHERE name = N'SPIDER_DB'

-- 查询数据表是否存在
SELECT 1 WHERE OBJECT_ID('dbo.TEST_TABLE', 'U') IS NOT NULL

-- 查询存储过程是否存在
SELECT 1 OBJECT_ID('dbo.TEST_PROC', 'P') IS NOT NULL
